package com.tnl.app.dao;

import java.util.List;

import com.tnl.app.entity.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.tnl.base.BaseDAO;
import com.tnl.utils.DateUtil;

@Repository
public class RecommendDAO {
	public static final int PAGE_SIZE = 10;
	private static Logger logger = LoggerFactory.getLogger(RecommendDAO.class);
	static final String SQL_SAVE = "insert into tb_recommend(personid"
			+ ",jobid,createby,createdate,status,reason, platformid, audioid,restype,phonecreatedate,shareopenid) values(?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE = "update tb_recommend set status=?,updatedate=? where id =?";

	private static final String SQL_GET_ALL = "select * from tb_recommend";
	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_recommend as r, tb_jobdesc as j where  r.jobId=j.id ";
	private static final String SQL_GET_ALL_WITH_LIMIT = "select r.*, name as jobName from tb_recommend as r, tb_jobdesc as j where r.jobId=j.id ";

	private static final String SQL_GETBYID = "select * from tb_recommend  where id = ?";
	private static final String SQL_DELETE = "delete from tb_recommend where id=?";

	private static final String SQL_GETCREATER = "select * from tb_recommend  where createby = ? order by jobid limit ?,? ";
	private static final String SQL_GET_PERSON = "select * from tb_recommend  where personid = ? order by jobid limit ?,?";
	private static final String SQL_GET_PERSON_AND_STATUS = "select a.id,a.status,a.createdate,b.id as 'jobId',b.`name` as 'positionName',c.`name` as 'companyName',b.annualPayment,d.`name` as 'domainName',\n" +
			"b.expiredDate,e.`name` as 'jobTitle',f.`name` as 'cityName', b.location  from tb_recommend as a \n" +
			"left JOIN tb_jobdesc as b on a.jobid = b.id\n" +
			"left join tb_company as c on c.id = b.companyid\n" +
			"LEFT JOIN tb_domain as d on d.id = b.domainid\n" +
			"left JOIN tb_jobtitle as e on e.id = b.roleid\n" +
			"LEFT JOIN tb_city as f on f.id = b.cityid\n" +
			" where a.personid = ? and a.status in( 6,7,8) \n" +
			"GROUP BY a.jobid order by a.id desc limit ?,?";
	private static final String SQL_GET_JOB_PERSON = "select * from tb_recommend  where jobid = ? and personid = ? and platformid=?";

	private static final String SQL_GET_RECOMMEND_POSITION = "SELECT distinct a.jobid AS jobid, b.name AS jobname, b.reward AS jobreward, c.name AS companyname, a.createby as createby from tb_recommend a, tb_jobdesc b, tb_company c where a.createby = ? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";

	private static final String SQL_GET_RECOMMENDED_PERSON = "select a.id AS id, a.status AS status, b.name AS name from tb_recommend a , tb_person b "
			+ "where a.personid = b.id and a.jobid=? and a.createby =? and a.platformid=? limit ?,?";

	private static final String SQL_GET_RECOMMENDED_PERSON_2 = "select a.id AS id, a.status AS status, b.name AS name, "
			+ "b.companyname as companyname,  b.companyid as companyid, "
			+ "c.name as companyname2, d.name as positionname "
			+ "from tb_recommend a , tb_jobtitle d, tb_person b "
			+ "left join tb_company c on b.companyid= c.id "
			+ "where a.personid = b.id and d.id=b.roleid and a.jobid=? and a.createby =? and a.platformid=? limit ?,?";

	private static final String SQL_GET_RECOMMEND_PERSON_S = "select a.id AS recommendid, a.status AS status, b.name AS jobname, b.reward AS jobreward,b.location as location,c.name AS companyname from tb_recommend a, tb_jobdesc b, tb_company c where a.personid = a.createby and a.createby = ? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";

	private static final String SQL_UPDTE_RESOURCE = "UPDATE tb_recommend SET resid=?, resstatus=? WHERE id=?";

	private static final String SQL_UPDTE_RESOURCE_AUDIO = "UPDATE tb_recommend SET audioid=? WHERE id=?";

	private static final String SQL_GET_JOB_PHONE = "select * from tb_recommend a, tb_person b  where a.personid = b.id and a.jobid = ? and b.phone = ? and a.status != 6";

	private static final String SQL_DOMAIN_MARCH = "select count(*) from tb_jobdesc where domainid in(?,?,?)";

	private static final String SQL_GET_RECOMMEND_PERSON_BY_JOBID = "select a.id AS id, a.status AS status, b.name AS name,"
			+ " b.companyname as companyname,  b.companyid as companyid, c.name as companyname2, d.name as positionname "
			+ "from tb_recommend a , tb_jobtitle d, tb_person b "
			+ "left join tb_company c on b.companyid=c.id where a.personid = b.id and d.id=b.roleid and a.jobid=? limit ?, ?";

	public int save(Recommendation recommend) {
		if (recommend == null) {
			logger.error("the input recommendation is null");
			return -1;
		}

		Long id = BaseDAO.save(SQL_SAVE, recommend.getPersonId(),
				recommend.getJobId(), recommend.getCreateby(),
				recommend.getCreateDate(), recommend.getStatus(),
				recommend.getReason(), recommend.getPlatformId(),
				recommend.getAudioid(),recommend.getRestype(),
				recommend.getPhoneCreateDate(),recommend.getShareopenid());
		if (id != null) {
			recommend.setId(id.intValue());
			return recommend.getId();
		}

		return -1;
	}

	public int delete(String recommendid) {
		if (StringUtils.isEmpty(recommendid)) {
			logger.error("please provide the recommendation id which will be removed.");
			return -1;
		}
		return BaseDAO.execute(SQL_DELETE, recommendid);
	}

	public List<Recommendation> getALL() {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_ALL);
	}

	public Long getDomainByMarch(int domain1, int domain2, int domain3) {
		return (Long) BaseDAO.queryOneRowOneCol(SQL_DOMAIN_MARCH, domain1,
				domain2, domain3);
	}

	public Long getCountOfAll(String name, Integer status) {
		String sql = SQL_GET_ALL_COUNT;
		if (null != status) {
			sql += " and r.status =" + status;
		}
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "'";
		}

		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}

	public List<Recommendation> getAllWithLimit(String name, Integer status,
			long start, long length) {
		String sql = SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "%'";
		}
		if (null != status) {
			sql += " and r.status =" + status + " ";
		}
		sql += " LIMIT ?, ?";
		return BaseDAO.queryForList(Recommendation.class, sql, start, length);
	}

	public int update(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDATE, rd.getStatus(),
				DateUtil.currentTime(), rd.getId());
	}

	public int updateStatus(int status, int recommendid) {
		return BaseDAO.execute(SQL_UPDATE, status, DateUtil.currentTime(),
				recommendid);
	}

	public int updateRes(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDTE_RESOURCE, rd.getResid(),
				rd.getResstatus(), rd.getId());
	}

	public int updateAudioRes(Recommendation rd) {
		return BaseDAO.execute(SQL_UPDTE_RESOURCE_AUDIO, rd.getAudioid(),
				rd.getId());
	}

	public Recommendation getById(String Id) {
		return BaseDAO.queryForObject(Recommendation.class, SQL_GETBYID, Id);
	}

	public List<Recommendation> getByCreater(int personId, int start) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GETCREATER,
				personId, start, PAGE_SIZE);
	}

	public List<Recommendation> getByPerson(Integer personId, int start) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_PERSON,
				personId, start, PAGE_SIZE);
	}

	public List<Recommendation> getByJobandPerson(long jobId, int personId,
			int platformid) {
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PERSON,
				jobId, personId, platformid);
	}

	public List<RecommendPosition> getRecommendPositionByCreater(
			int platformid, int personId, int start) {
		return BaseDAO.queryForList(RecommendPosition.class,
				SQL_GET_RECOMMEND_POSITION, personId, platformid, start,
				PAGE_SIZE);
	}

	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(
			int platformid, int personId, int jobId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMENDED_PERSON, jobId, personId, platformid, start,
				2);
	}

	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(
			int platformid, int personId, int jobId, int start, int length) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMENDED_PERSON, jobId, personId, platformid, start,
				length);
	}

	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater2(
			int platformid, int personId, long jobId, int start) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMENDED_PERSON_2, jobId, personId, platformid,
				start, PAGE_SIZE);
	}

	public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater2(
			int platformid, int personId, long jobId, int start, int length) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMENDED_PERSON_2, jobId, personId, platformid,
				start, length);
	}

	public List<SelfRecommendPosition> getSelfRecommendPositionByCreater(
			int platformid, int personId, int start) {
		return BaseDAO.queryForList(SelfRecommendPosition.class,
				SQL_GET_RECOMMEND_PERSON_S, personId, platformid, start,
				PAGE_SIZE);
	}

	private static final String SQL_GET_JOB_PHONE_PLAT = "select * from tb_recommend a, tb_person b  where a.personid = b.id "
			+ "and a.jobid = ? and b.phone = ? and b.platformid =? ";

	public Recommendation getByJobAndPhone(long jobId, String phone,
			int platformid) {
		return BaseDAO.queryForObject(Recommendation.class,
				SQL_GET_JOB_PHONE_PLAT, jobId, phone, platformid);
	}

	public RecommendedPerson getByJobAndPhone(long jobId, String phone) {
		return BaseDAO.queryForObject(RecommendedPerson.class,
				SQL_GET_JOB_PHONE, jobId, phone);
	}

	public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId,
			int start, int length) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, length);
	}

	public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId,
			int start) {
		return BaseDAO.queryForList(RecommendedPerson.class,
				SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, PAGE_SIZE);
	}
	
	final String SQL_GET_BY_JOBANDPHONE="select * from tb_recommend a,tb_person_recom b where a.personid=b.id and a.jobid=? and b.id=?";
	public List<Recommendation> getByJobAndPerson(int jobId,int personId){
		return BaseDAO.queryForList(Recommendation.class, SQL_GET_BY_JOBANDPHONE, jobId,personId);
	}


	public List<Recommend> getByPersonAndStatus(Integer personId,int start) {
		return BaseDAO.queryForList(Recommend.class, SQL_GET_PERSON_AND_STATUS,
				personId,start, PAGE_SIZE);
	}

	public Employer getEmployerById(Integer id) {
		return BaseDAO.queryForObject(Employer.class, "select * from tb_employer where id = ? ", new Object[]{id});
	}
}
